﻿/*
    Copyright 2013 Bob Moss (bobbyjmoss@gmail.com)

   Licensed under the Apache License, Version 2.0 (the "License");
   you may not use this file except in compliance with the License.
   You may obtain a copy of the License at

       http://www.apache.org/licenses/LICENSE-2.0

   Unless required by applicable law or agreed to in writing, software
   distributed under the License is distributed on an "AS IS" BASIS,
   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
   See the License for the specific language governing permissions and
   limitations under the License.
 */

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace OpenRAMS.Model
{
    public class WorkCategories
    {
        private List<String> categories;

        public List<String> getCategories()
        {
            return categories;  //Deliberately abstracted. Adds/deletes are handled by functions that act on DB then reload list.
        }

        //internal class variables/arrays
        private String attribName;  //only one field in a look-up table!
        private String tableName;

        //database-related variables
        private SqlConnection dbConnection;
        private SqlDataReader dbReader;
        private SqlCommand dbCommand;
        private String dbInString;

        public WorkCategories()
        {
            //db-related initialisations
            initHardCodedVals();
            try { dbInString = ConfigurationManager.ConnectionStrings["default"].ConnectionString; }
            catch (Exception e) { throw e; }  //this will cause error conditions later

            Load(); //loads in list values from database
        }

        private void Load()
        {
            categories = new List<String>();

            String queryFull = "SELECT * FROM " + tableName, outString = "";

            //attempt DB connection
            try
            {
                dbConnection = new SqlConnection(dbInString);
                dbConnection.Open();
            }
            catch (Exception e) { throw e; }

            //create command that will run query
            dbCommand = dbConnection.CreateCommand();
            dbCommand.CommandText = queryFull;
            dbCommand.CommandType = CommandType.Text;
            dbCommand.CommandTimeout = 15;

            //Run command, and tidy-up if exceptions occur
            try { dbReader = dbCommand.ExecuteReader(); }
            catch(Exception e)
            {
                dbReader.Close();
                dbConnection.Close();
                throw e;
            }

            //Import values from query into string list of work categories
            while (dbReader.Read())
            {
                try 
                { 
                    outString = dbReader[attribName].ToString();
                    categories.Add(outString);
                }
                catch(Exception e) { throw e; }
            }

            //Clean-up DB connection
            try
            {
                dbReader.Close();
                dbConnection.Close();
            }
            catch (Exception e) { throw e; }
        }

        public int Find(String catSearch)
        {
            int test = categories.IndexOf(catSearch);
            if (test < 0) { return -1; }    //if element doesn't exist, -1 is returned & also the error value returned for this method
            return 0;
        }

        public int Add(String newCat)
        {
            //Ensure parameter is valid
            if (newCat == "" || newCat == " " || newCat == "*") { return -1; }

            //verify entry doesn't already exist
            int existTest = Find(newCat);
            if (existTest > -1) { return -1; }

            //Build SQL Query
            String queryFull = "INSERT INTO " + tableName + " VALUES ('" + newCat + "')";

            //attempt DB connection
            try
            {
                dbConnection = new SqlConnection(dbInString);
                dbConnection.Open();
            }
            catch { return -1; }

            //create command that will run query
            dbCommand = dbConnection.CreateCommand();
            dbCommand.CommandText = queryFull;
            dbCommand.CommandType = CommandType.Text;
            dbCommand.CommandTimeout = 15;

            //Run command, and tidy-up if exceptions occur
            try { dbReader = dbCommand.ExecuteReader(); }
            catch
            {
                dbReader.Close();
                dbConnection.Close();
                return -1;
            }

            //check it was added successfully
            Load();
            existTest = Find(newCat);
            if (existTest < 0) { return -1; }
            return 0;
        }

        public int Delete(String delCat)
        {
            //Ensure parameter is valid
            if (delCat == "" || delCat == " " || delCat == "*") { return -1; }

            //Verify the entry exists
            int existTest = Find(delCat);
            if (existTest < 0) { return -1; }

            /* UNCOMMENT LINES BELOW ONCE WORK LOG CLASS EXISTS */
            //Verify there are no work logs/activities in this category
            //WorkLog test = new WorkLog();
            //int wlTest = test.Search(2, delCat);
            //if (wlTest < 0) { return -1; }

            //Build SQL Query
            String queryFull = "DELETE FROM " + tableName, queryCondition = " WHERE " + attribName + "='" + delCat + "'";

            //attempt DB connection
            try
            {
                dbConnection = new SqlConnection(dbInString);
                dbConnection.Open();
            }
            catch { return -1; }

            //create command that will run query
            dbCommand = dbConnection.CreateCommand();
            dbCommand.CommandText = queryFull;
            dbCommand.CommandType = CommandType.Text;
            dbCommand.CommandTimeout = 15;

            //Run command, and tidy-up if exceptions occur
            try { dbReader = dbCommand.ExecuteReader(); }
            catch
            {
                dbReader.Close();
                dbConnection.Close();
                return -1;
            }

            //Check was deleted correctly
            Load();
            existTest = Find(delCat);
            if (existTest > -1) { return -1; }

            return 0;
        }

        protected void initHardCodedVals()
        {
            //set hard-coded values
            tableName = "tblWorkCategories";
            attribName = "txtWorkCategory";
        }
    }
}